﻿#include "precomp.h"
#include "jsqltablemgr.h"
#include <QSqlDriver>
#include <QSqlDatabase>
#include <QSqlIndex>
#include <QSqlQuery>

// - class JSqlTableMgrPrivate -

class JSqlTableMgrPrivate
{
    J_DECLARE_PUBLIC(JSqlTableMgr)
    public:
        JSqlTableMgrPrivate(JSqlTableMgr *parent) :
      q_ptr(parent)
    {
        //db.transaction();
        //db.commit();
    }

private:
    QSqlDatabase db;
    QString filePath;   // 配置文件路径

    // 数据库配置信息（来自配置文件）
    struct DatabaseConfig {
        QString type;
        QString connectionName;
        QString databaseName;
        QString userName;
        QString password;
    } config;

    //QMutex mutex;
};

// - class JSqlTableMgr -

J_IMPLEMENT_SINGLE_INSTANCE(JSqlTableMgr)

JSqlTableMgr::JSqlTableMgr() :
    d_ptr(new JSqlTableMgrPrivate(this))
{

}

JSqlTableMgr::~JSqlTableMgr()
{
    delete d_ptr;
}

QSqlDatabase JSqlTableMgr::database() const
{
    Q_D(const JSqlTableMgr);
    return d->db;
}

bool JSqlTableMgr::isValid() const
{
    Q_D(const JSqlTableMgr);
    return d->db.isValid();
}

bool JSqlTableMgr::transaction()
{
    Q_D(JSqlTableMgr);
    return d->db.transaction();
}

bool JSqlTableMgr::commit()
{
    Q_D(JSqlTableMgr);
    return d->db.commit();
}

bool JSqlTableMgr::rollback()
{
    Q_D(JSqlTableMgr);
    return d->db.rollback();
}

QString JSqlTableMgr::databaseName() const
{
    Q_D(const JSqlTableMgr);
    return d->db.databaseName();
}

bool JSqlTableMgr::loadConfig(const QString &filePath)
{
    Q_D(JSqlTableMgr);
    d->filePath = filePath;

    QFile file(filePath);
    if (!file.open(QFile::ReadOnly)) {
        qCritical() << QStringLiteral("文件打开失败！(%1)").arg(file.errorString());
        return false;
    }

    QString errorMsg;
    int errorLine = -1, errorColumn = -1;
    QDomDocument document;
    if (!document.setContent(&file, &errorMsg, &errorLine, &errorColumn)) {
        qCritical() << QStringLiteral("文件%1解析失败！(错误信息: %2，错误位置：(%3, %4))")
                       .arg(filePath)
                       .arg(errorMsg)
                       .arg(errorLine)
                       .arg(errorColumn);
        return false;
    }

    QDomElement emRoot = document.firstChildElement("config");
    if (emRoot.isNull()) {
        qCritical() << QStringLiteral("根节点[config]未找到！");
        return false;
    }

    QDomElement emDatabase = emRoot.firstChildElement("database");
    if (emDatabase.isNull()) {
        qCritical() << QStringLiteral("子节点[database]未找到！");
        return false;
    }

    d->config.type = emDatabase.attribute("type", "QSQLITE");
    d->config.connectionName = emDatabase.attribute("connectionName", "local-data-record");
    d->config.databaseName = emDatabase.attribute("databaseName", "records.db");

    QDomElement emAccount = emDatabase.firstChildElement("account");
    if (emAccount.isNull()) {
        qCritical() << QStringLiteral("子节点[account]未找到！");
        return false;
    }

    d->config.userName = emAccount.attribute("userName", "123");
    d->config.password = emAccount.attribute("password", "123");

    // 结束配置文件解析
    file.close();
    document.clear();

    // create database objects

    if (d->db.isOpen()) {
        d->db.close();
    }

    if (QSqlDatabase::contains(d->config.connectionName)) {
        QSqlDatabase::removeDatabase(d->config.connectionName);
    }

    // 删除已有数据库[文件]
    if (d->config.type == "QSQLITE") {
        //QFile(d->config.databaseName).remove();
    }

    d->db = QSqlDatabase::addDatabase(d->config.type, d->config.connectionName);
    d->db.setDatabaseName(d->config.databaseName);
    d->db.setUserName(d->config.userName);
    d->db.setPassword(d->config.password);

    //
    if (d->config.type == "QSQLITE") {
        if (!QFile::exists(d->config.databaseName)) {
            QDir().mkpath(QFileInfo(d->config.databaseName).absolutePath());
        }
    }

    if (!d->db.isValid()) {
        qCritical() << QStringLiteral("数据库无效！");
        return false;
    }

    // 打开数据库
    if (!d->db.open()) {
        qCritical() << QStringLiteral("数据库打开失败！(类型：%1，连接名称：%2，数据库名称：%3，错误信息：%4)")
                       .arg(d->config.type)
                       .arg(d->config.connectionName)
                       .arg(d->config.databaseName)
                       .arg(d->db.lastError().text());
        return false;
    }

    //
    d->db.exec("PRAGMA synchronous=OFF;");
    //
    //d->db.exec("PRAGMA default_cache_size=8000");
    //
    //d->db.exec("PRAGMA auto_vacuum=NONE;");

    // 创建主表
    if (!createMainTable()) {
        qCritical() << QStringLiteral("创建主表失败！");
        return false;
    }

    // 创建账号管理表
    if (!createAccountTable()) {
        qCritical() << QStringLiteral("创建账号表失败！");
        return false;
    }
#if 0
    QStringList columnNames0 = tableColumns(mainTableName());

    QStringList columnNames;
    QSqlIndex index = d->db.primaryIndex(mainTableName());
    int count = index.count();
    for (int i = 0; i < 5; ++i) {
        columnNames.append(index.fieldName(i));
    }
#endif
    return true;
}

bool JSqlTableMgr::existsTable(const QString &tableName) const
{
    if (tableName.isEmpty()) {
        qCritical() << QStringLiteral("表名称不能为空！");
        return false;
    }

    Q_D(const JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    Q_ASSERT(d->config.type == "QSQLITE");  //! 如果换成其他数据库，此条命令需要修改
    QSqlQuery query(d->db);
    bool result = query.exec(QString("select count(*) from sqlite_master where type='table'"
                                     " and name='%1'")
                             .arg(tableName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    if (query.next()) {
        if (query.value(0).toInt() > 0) {
            return true;
        }
    }

    return false;
}

QStringList JSqlTableMgr::tableColumns(const QString &tableName) const
{
    //Q_ASSERT(false);    // 暂时未实现
    QStringList columnNames;

    if (tableName.isEmpty()) {
        qCritical() << QStringLiteral("表名称不能为空！");
        return columnNames;
    }

    Q_D(const JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return columnNames;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("PRAGMA table_info(%1)")
                             .arg(tableName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return columnNames;
    }

    // 读取数据
    while (query.next()) {
        QSqlRecord record = query.record();
        if (record.isEmpty()) {
            continue;
        }

        // 获取列name字段值
        QVariant v = record.value(1);
        if (v.isNull()) {
            continue;
        }

        // 获取字段名称
        columnNames.append(v.toString());
    }

    return columnNames;
}

bool JSqlTableMgr::createMainTable()
{
    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    if (existsTable(mainTableName())) {
        //qWarning() << QStringLiteral("数据库表[%1]已存在！").arg(mainTableName());
        return true;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("CREATE TABLE %1("
                                     "JX VARCHAR(300) NOT NULL,"
                                     "JH VARCHAR(300) NOT NULL,"
                                     "RQ DATE NOT NULL,"
                                     "JC INTEGER NOT NULL,"
                                     "DYXHB VARCHAR(100) NOT NULL,"
                                     "DYZB VARCHAR(100) NOT NULL,"
                                     "DYRDFW VARCHAR(100) NOT NULL)")
                             .arg(mainTableName()));
    if (!result) {
        qCritical() << QStringLiteral("创建主表失败！");
        return false;
    }

    return true;
}

bool JSqlTableMgr::createSignalTable(const QString &tableName)
{
    if (tableName.isEmpty()) {
        qCritical() << QStringLiteral("表名不能为空！");
        return false;
    }

    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    if (existsTable(tableName)) {
        qCritical() << QStringLiteral("数据库表[%1]已存在！").arg(tableName);
        return false;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("CREATE TABLE %1("
                                     "YXHM VARCHAR(255) PRIMARY KEY UNIQUE ON CONFLICT REPLACE,"
                                     "BM VARCHAR(255),"
                                     "SSJX VARCHAR(300) NOT NULL,"
                                     "SSJH VARCHAR(300) NOT NULL,"
                                     "SSRQ DATETIME NOT NULL,"
                                     "SSJC INTEGER NOT NULL,"
                                     "SXBZ INTEGER(1) NOT NULL,"
                                     "GJBZ INTEGER(1) NOT NULL,"
                                     "XZBZ INTEGER(1) NOT NULL);")
                             .arg(tableName));
    if (!result) {
        qCritical() << QStringLiteral("创建主表失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::createRecordTable(const QString &tableName, const QStringList &columnNames)
{
    if (tableName.isEmpty()) {
        qCritical() << QStringLiteral("表名不能为空！");
        return false;
    }

    if (existsTable(tableName)) {
        return false;
    }

    if (columnNames.isEmpty()) {
        qCritical() << QStringLiteral("变量列表不能为空！");
        return false;
    }

    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    //
    QStringList symbols = columnNames;
    QString timeName = symbols.takeFirst();     // 第一个为时间量，取出

    QString sQuery;
    if (symbols.isEmpty()) {
        sQuery = QString("CREATE TABLE %1("
                         "%2 TIME UNIQUE NOT NULL)")
                .arg(tableName)
                .arg(timeName);
    } else {
        sQuery = QString("CREATE TABLE %1("
                         "%2 TIME UNIQUE NOT NULL,"
                         "%3 DOUBLE)")
                .arg(tableName)
                .arg(timeName)
                .arg(symbols.join(" DOUBLE,"));
    }

    QSqlQuery query(d->db);
    bool result = query.exec(sQuery);
    if (!result) {
        qCritical() << QStringLiteral("创建数据表失败！[%1]").arg(query.lastError().text());
        return false;
    }
#if 0
    // create index
    result = query.exec(QString("create unique index time_id on %1(%2)")
                        .arg(tableName).arg(timeName));
    if (!result) {
        qCritical() << QStringLiteral("创建数据表时间金索引失败！[%1]").arg(query.lastError().text());
        return false;
    }
#endif
    return true;
}

bool JSqlTableMgr::createHotSpotTable(const QString &tableName)
{
    if (tableName.isEmpty()) {
        qCritical() << QStringLiteral("表名不能为空！");
        return false;
    }

    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    if (existsTable(tableName)) {
        return true;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("CREATE TABLE %1("
                                     " XMIN DOUBLE NOT NULL,"
                                     " XMAX DOUBLE NOT NULL,"
                                     " YMIN DOUBLE NOT NULL,"
                                     " YMAX DOUBLE NOT NULL)")
                             .arg(tableName));
    if (!result) {
        qCritical() << QStringLiteral("创建主表失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

int JSqlTableMgr::rowCountOfTable(const QString &tableName)
{
    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select count(*) from %1")
                             .arg(tableName));
    if (!result) {
        qCritical() << QStringLiteral("增加信号列失败！[%1]").arg(query.lastError().text());
        return 0;
    }

    if (query.next()) {
        return query.value(0).toInt();
    }

    return 0;
}

bool JSqlTableMgr::addRecordSymbol(const QString &tableName, const QStringList &symbols)
{
    Q_D(JSqlTableMgr);

    // 检查参数有效性
    if ( symbols.isEmpty()) {
        return true;        // 没有列需要添加
    }

    // 向表tableName增加信号量symbols
    QSqlQuery query(d->db);
    bool result = query.exec(QString("ALTER TABLE %1 ADD COLUMN %2 DOUBLE")
                             .arg(tableName)
                             .arg(symbols.join(" DOUBLE,")));
    if (!result) {
        qCritical() << QStringLiteral("增加信号列失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::removeRecordSymbol(const QString &tableName, const QStringList &symbols)
{
    Q_D(JSqlTableMgr);

    // 检测表是否存在
    if (!existsTable(tableName)) {
        qCritical() << QStringLiteral("数据库表[%1]不存在！").arg(tableName);
        return false;
    }

    // 获取表所有列
    QStringList oldSymbols = tableColumns(tableName);

    // 检查获取的列是否有效
    if (oldSymbols.count() < 1) {
        qCritical() << QStringLiteral("据库表[%1]的列个数不正确，必须>=1").arg(tableName);
        return false;   // 数据库表的列不能为空
    }

    // 检查是否有信号量需要处理
    if (oldSymbols.count() == 1) {
        return true;    // 没有信号量需要删除
    }

    // 暂存TIME字段名称
    QString columnTime = oldSymbols.takeFirst();

    // 记录原有信号量个数
    int oldSymbolCount = oldSymbols.count();

    // 将oldSymbols在symbols中出现的信号量删除
    QStringListIterator citerOldSymbols(oldSymbols);
    while (citerOldSymbols.hasNext()) {
        const QString &oldSymbol = citerOldSymbols.next();
        if (symbols.contains(oldSymbol)) {
            oldSymbols.removeAll(oldSymbol);
        }
    }

    // 检查筛选后的信号量删除条件
    if (oldSymbolCount == oldSymbols.count()) {
        return true;   // 没有信号量需要删除
    }

    //
    QSqlQuery query(d->db);

    //
    d->db.transaction();

    // 1.生成新表
    bool result = query.exec(QString("CREATE TABLE %1_TEMP AS SELECT %2,%3 FROM %1")
                             .arg(tableName)
                             .arg(columnTime)
                             .arg(oldSymbols.join(',')));
    if (!result) {
        d->db.rollback();
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    // 2.删除旧表
    result = query.exec(QString("DROP TABLE %1").arg(tableName));
    if (!result) {
        d->db.rollback();
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    // 3.更改表名
    result = query.exec(QString("ALTER TABLE %1_TEMP RENAME TO %1").arg(tableName));
    if (!result) {
        d->db.rollback();
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    //
    d->db.commit();

    return true;
}

bool JSqlTableMgr::updateRecordSymbol(const QString &tableName, const QStringList &symbols,
                                      bool isIgnoreSift)
{
    // 检测表是否存在
    if (!existsTable(tableName)) {
        qCritical() << QStringLiteral("数据库表[%1]不存在！").arg(tableName);
        return false;
    }

    // 获取表所有列
    QStringList oldSymbols = tableColumns(tableName);

    // 检查获取的列是否有效
    if (oldSymbols.count() < 1) {
        qCritical() << QStringLiteral("据库表[%1]的列个数不正确，必须>=1").arg(tableName);
        return false;   // 数据库表的列不能为空
    }

    Q_D(JSqlTableMgr);

    //
    QSqlQuery query(d->db);
    bool result = false;

    //
    d->db.transaction();

    // 需要增加的信号量
    QStringList addSymbols = symbols;

    // 检查是否有信号量需要处理
    if (oldSymbols.count() > 1) {
        // 暂存TIME字段名称
        QString columnTime = oldSymbols.takeFirst();

        // 记录原有信号量个数
        int oldSymbolCount = oldSymbols.count();

        // 筛选信号量。将symbols在oldSymbols中不存在的列名选出
        QStringListIterator citerOldSymbols(oldSymbols);
        while (citerOldSymbols.hasNext()) {
            const QString &oldSymbol = citerOldSymbols.next();
            if (symbols.contains(oldSymbol)) {
                // 将oldSymbols在symbols中存在的信号量从addSymbols中删除
                addSymbols.removeAll(oldSymbol);
            } else if (!isIgnoreSift) {
                // 将oldSymbols在symbols中不存在的信号量从oldSymbols中删除
                //oldSymbols.removeAll(oldSymbol);
            }
        }

        // 检查筛选后的信号量删除条件
        if (oldSymbolCount > oldSymbols.count()) {
            // 1.生成新表
            QStringList newSymbols = oldSymbols;
            newSymbols.prepend(columnTime);
            result = JSqlTableMgr::instance()->createRecordTable(tableName + "_tmp", newSymbols);
            if (!result) {
                d->db.rollback();
                qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
                return false;
            }
            // 拷贝数据
            if (oldSymbols.isEmpty()) {
                result = query.exec(QString("insert into %1_tmp(%2) select %2 from %1")
                                    .arg(tableName).arg(columnTime));
            } else {
                result = query.exec(QString("insert into %1_tmp(%2,%3) select %2,%3 from %1")
                                    .arg(tableName).arg(columnTime)
                                    .arg(oldSymbols.join(',')));
            }
            if (!result) {
                d->db.rollback();
                qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
                return false;
            }
            // 2.删除旧表
            result = query.exec(QString("drop table %1").arg(tableName));
            if (!result) {
                d->db.rollback();
                qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
                return false;
            }

            // 3.更改表名
            result = query.exec(QString("alter table %1_tmp rename to %1").arg(tableName));
            if (!result) {
                d->db.rollback();
                qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
                return false;
            }
        }
    }

    // 检查是否有信号量需要增加
    if (!addSymbols.isEmpty()) {
        // 向表tableName增加列addSymbols
        QStringListIterator citerAddSymbols(addSymbols);
        while (citerAddSymbols.hasNext()) {
            const QString &addSymbol = citerAddSymbols.next();
            result = query.exec(QString("alter table %1 add %2 double")
                                .arg(tableName).arg(addSymbol));
            if (!result) {
                d->db.rollback();
                qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
                return false;
            }
        }
    }

    //
    d->db.commit();

    return true;
}

bool JSqlTableMgr::clearDatabase()
{
    Q_D(JSqlTableMgr);
    return loadConfig(d->filePath);
}

bool JSqlTableMgr::clearTable(const QString &tableName)
{
    if (tableName.isEmpty()) {
        qCritical() << QStringLiteral("表名称不能为空！");
        return false;
    }

    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    QSqlQuery query(d->db);
    bool result = false;

    if (d->config.type == "QSQLITE") {
        // 清除表数据
        result = query.exec(QString("DELETE FROM %1").arg(tableName));
        if (!result) {
            qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
            return false;
        }
        // 复位自增计数
        result = query.exec(QString("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='%1'").arg(tableName));
        if (!result) {
            qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
            return false;
        }
    } else {
        result = query.exec(QString("TRUNCATE TABLE %1").arg(tableName));
        if (!result) {
            qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
            return false;
        }
    }

    return true;
}

bool JSqlTableMgr::deleteTable(const QString &tableName)
{
    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("DROP TABLE %1").arg(tableName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::renameTable(const QString &tableName, const QString &newTableName)
{
    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("ALTER TABLE %1 RENAME TO %2")
                             .arg(tableName).arg(newTableName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::open()
{
    Q_D(JSqlTableMgr);
    if (!d->db.isValid()) {
        return false;
    }

    if (d->db.isOpen()) {
        return true;
    }

    return d->db.open();
}

void JSqlTableMgr::close()
{
    Q_D(JSqlTableMgr);
    if (d->db.isOpen()) {
        d->db.close();
    }
}

QString JSqlTableMgr::databaseType() const
{
    Q_D(const JSqlTableMgr);
    return d->config.type;
}

QString JSqlTableMgr::connectionName() const
{
    Q_D(const JSqlTableMgr);
    return d->config.connectionName;
}

QString JSqlTableMgr::mainTableName()
{
    return "TBL_SJJCWH";
}

QString JSqlTableMgr::signalTableNamePrefix()
{
    return "TBL_DYXHB_";
}

QString JSqlTableMgr::recordTableNamePrefix()
{
    return "TBL_DYZB_";
}

QString JSqlTableMgr::hotSpotTableNamePrefix()
{
    return "TBL_DYRDFW_";
}

QString JSqlTableMgr::suffixOfSubTable(const QString &tableName)
{
    return tableName.section('_', 2);
}

QString JSqlTableMgr::generateTableSuffix(const JFileNameInfo &info)
{
    return info.JX % info.JH
            % '_' % info.minRQ.toString("yyyyMMdd")
            % '_' % QString::number(info.JC);
}

QString JSqlTableMgr::generateSignalTableName(const JFileNameInfo &info)
{
    return signalTableNamePrefix().append(generateTableSuffix(info));
}

QString JSqlTableMgr::generateRecordTableName(const JFileNameInfo &info)
{
    return recordTableNamePrefix().append(generateTableSuffix(info));
}

QString JSqlTableMgr::generateHotSpotTableName(const JFileNameInfo &info)
{
    return hotSpotTableNamePrefix().append(generateTableSuffix(info));
}

QString JSqlTableMgr::baseLineTableName()
{
    return "TBL_BASELINE";
}

bool JSqlTableMgr::createBaseLineTable()
{
    Q_D(JSqlTableMgr);

    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    if (existsTable(baseLineTableName())) {
        qCritical() << QStringLiteral("数据库表[%1]已存在！").arg(baseLineTableName());
        return true;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("CREATE TABLE %1("
                                     "XH INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                                     "GROUPID INTEGER NOT NULL,"
                                     "X DOUBLE NOT NULL,"
                                     "Y DOUBLE NOT NULL)")
                             .arg(baseLineTableName()));
    if (!result) {
        qCritical() << QStringLiteral("创建母线表失败！");
        return false;
    }

    return true;
}

QString JSqlTableMgr::accountTableName() const
{
    return "TBL_ACCOUNT";
}

bool JSqlTableMgr::createAccountTable()
{
    Q_D(JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    if (existsTable(accountTableName())) {
        //qWarning() << QStringLiteral("数据库表[%1]已存在！").arg(accountTableName());
        return true;
    }

    QSqlQuery query(d->db);
    bool result = query.exec(QString("CREATE TABLE %1("
                                     "XH INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                                     "USERNAME VARCHAR(100) NOT NULL,"
                                     "PASSWORD VARCHAR(100) NOT NULL,"
                                     "USERTYPE INTEGER NOT NULL,"
                                     "SECURITY INTEGER)")
                             .arg(accountTableName()));
    if (!result) {
        qCritical() << QStringLiteral("创建账号表失败！");
        return false;
    }

    return true;
}

QStringList JSqlTableMgr::allUserName() const
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return QStringList();
    }

    // 查询所有USERNAME字段数据
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select distinct USERNAME from %1 order by USERTYPE")
                             .arg(accountTableName()));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return QStringList();
    }

    // 读取数据
    QStringList l;
    while (query.next()) {
        l << query.value(0).toString();
    }

    return l;
}

bool JSqlTableMgr::readAllUser(QList<JAccountInfo> &infos) const
{
    infos.clear();
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    // 查询所有USERNAME字段数据
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select distinct XH,USERNAME,PASSWORD,USERTYPE,SECURITY"
                                     " from %1 order by USERTYPE")
                             .arg(accountTableName()));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    // 读取数据
    while (query.next()) {
        JAccountInfo info;
        info.XH = query.value(0).toString();
        info.userName = query.value(1).toString();
        info.password = query.value(2).toString();
        info.userType = (AccountUserType)query.value(3).toInt();
        info.security = query.value(4).toUInt();
        infos.append(info);
    }

    return true;
}

bool JSqlTableMgr::readAllUser(QList<JAccountInfo> &infos, AccountUserType type) const
{
    infos.clear();
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    // 查询所有USERNAME字段数据
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select distinct XH,USERNAME,PASSWORD,USERTYPE,SECURITY"
                                     " from %1 where USERTYPE=%2 order by USERNAME")
                             .arg(accountTableName()).arg(type));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    // 读取数据
    while (query.next()) {
        JAccountInfo info;
        info.XH = query.value(0).toString();
        info.userName = query.value(1).toString();
        info.password = query.value(2).toString();
        info.userType = (AccountUserType)query.value(3).toInt();
        info.security = query.value(4).toUInt();
        infos.append(info);
    }

    return true;
}

bool JSqlTableMgr::existsUser(const QString &userName) const
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    // 查询所有USERNAME字段数据
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select count(*) from %1 where USERNAME='%2'")
                             .arg(accountTableName()).arg(userName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    if (query.next()) {
        if (query.value(0).toInt() > 0) {
            return true;
        }
    }

    return false;
}

bool JSqlTableMgr::checkUserName(const QString &userName, const QString &password) const
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select count(*) from %1 where"
                                     " USERNAME='%2' and PASSWORD='%3'")
                             .arg(accountTableName()).arg(userName).arg(password));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    if (query.next()) {
        if (query.value(0).toInt() > 0) {
            return true;
        }
    }

    return false;
}

AccountUserType JSqlTableMgr::userType(const QString &userName) const
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return AccountUserInvalid;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select USERTYPE from %1 where USERNAME='%2'")
                             .arg(accountTableName()).arg(userName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return AccountUserInvalid;
    }

    if (query.next()) {
        return (AccountUserType)query.value(0).toInt();
    }

    return AccountUserInvalid;
}

bool JSqlTableMgr::setUserType(const QString &userName, AccountUserType type)
{
    Q_D(JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("update %1 set USERTYPE=%2 where USERNAME='%3'")
                             .arg(accountTableName()).arg(userName).arg((int)type));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::addUser(const JAccountInfo &info)
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return true;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = true;
    if (info.XH.isEmpty()) {
        result = query.exec(QString("insert into %1(USERNAME,PASSWORD,USERTYPE,SECURITY)"
                                    " values('%2','%3',%4,%5)")
                            .arg(accountTableName())
                            .arg(info.userName)
                            .arg(info.password)
                            .arg(info.userType)
                            .arg(info.security));
    } else {
        result = query.exec(QString("update %1 set USERNAME='%2',PASSWORD='%3',"
                                    "USERTYPE=%4,SECURITY=%5")
                            .arg(accountTableName())
                            .arg(info.userName)
                            .arg(info.password)
                            .arg(info.userType)
                            .arg(info.security));
    }
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::removeUser(const QString &userName)
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return true;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("delete from %1 where USERNAME='%2'")
                             .arg(accountTableName()).arg(userName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}

bool JSqlTableMgr::clearUser()
{
    return clearTable(accountTableName());
}

unsigned int JSqlTableMgr::userSecurity(const QString &userName) const
{
    Q_D(const JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return 0;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("select SECURITY from %1 where USERNAME='%2'")
                             .arg(accountTableName()).arg(userName));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return 0;
    }

    if (query.next()) {
        return query.value(0).toUInt();
    }

    return 0;
}

bool JSqlTableMgr::setUserSecurity(const QString &userName, unsigned int security)
{
    Q_D(JSqlTableMgr);
    if (!d->db.isOpen()) {
        qCritical() << QStringLiteral("数据库未打开！");
        return false;
    }

    //
    QSqlQuery query(d->db);
    query.setForwardOnly(true);
    bool result = query.exec(QString("update %1 set SECURITY=%3 where USERNAME='%2'")
                             .arg(accountTableName()).arg(userName).arg((int)security));
    if (!result) {
        qCritical() << QStringLiteral("命令执行失败！[%1]").arg(query.lastError().text());
        return false;
    }

    return true;
}
